package com.fzy.Source;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DB {
	public static Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/im", "root", "");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}

	public static User getUser(int id) {
		User user = new User();
		Connection connection = getConnection();
		String sql = "select * from users where id=?";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, id);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPw(rs.getString("pw"));
				user.setSign(rs.getString("sign"));
			}

			rs.close();
			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}

	public static int addUser(User user) {
		Connection connection = getConnection();
		String sql = "insert into users(name,pw,sign) values(?,?,?)";
		String sql2 = "select id from users where name=?";
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);

			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPw());
			pstmt.setString(3, user.getSign());

			pstmt.executeUpdate();

			PreparedStatement pstmt2 = connection.prepareStatement(sql2);
			pstmt2.setString(1, user.getName());

			ResultSet rs = pstmt2.executeQuery();
			if (rs.next())
				user.setId(rs.getInt("id"));

			rs.close();
			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user.getId();
	}

	public static boolean checkName(String name) {
		Connection connection = getConnection();
		String sql = "select name from users where name=?";
		boolean flag = true;
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, name);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next() && name.equals(rs.getString(1)))
				flag = false;

			rs.close();
			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return flag;
	}

	public static boolean addFriend(int id, int fid) {
		if (id == fid)
			return false;
		String sql1 = "select id from users where id=?";
		String sql2 = "select fid from friends where id=? and fid=?";
		String sql3 = "insert into friends values(?,?)";
		boolean flag = false;
		try {
			Connection connection = DB.getConnection();
			PreparedStatement pstmt = connection.prepareStatement(sql1);
			pstmt.setInt(1, id);

			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				rs.close();
				pstmt.close();

				pstmt = connection.prepareStatement(sql2);
				pstmt.setInt(1, id);
				pstmt.setInt(2, fid);

				rs = pstmt.executeQuery();
				if (!rs.next()) {
					pstmt.close();

					pstmt = connection.prepareStatement(sql3);
					pstmt.setInt(1, id);
					pstmt.setInt(2, fid);
					pstmt.executeUpdate();

					pstmt.setInt(1, fid);
					pstmt.setInt(2, id);
					pstmt.executeUpdate();
					flag = true;
				}
			}
			rs.close();
			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public static boolean deleteFriend(int id, int fid) {
		String sql1 = "select fid from friends where id=? and fid=?";
		String sql2 = "delete from friends where id=? and fid=?";
		String sql3 = "delete from friends where id=? and fid=?";

		boolean flag = false;
		try {
			Connection connection = getConnection();
			PreparedStatement pstmt = connection.prepareStatement(sql1);
			pstmt.setInt(1, id);
			pstmt.setInt(2, fid);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				pstmt.close();

				pstmt = connection.prepareStatement(sql2);
				pstmt.setInt(1, id);
				pstmt.setInt(2, fid);
				pstmt.executeUpdate();
				pstmt.close();
				pstmt = connection.prepareStatement(sql3);
				pstmt.setInt(1, fid);
				pstmt.setInt(2, id);

				pstmt.executeUpdate();

				flag = true;
			}

			rs.close();
			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return flag;
	}

	public static void saveNoIPMessage(int id, String message) {
		Connection connection = getConnection();
		String sql1 = "insert into messages values(?,?)";

		try {
			PreparedStatement pstmt = connection.prepareStatement(sql1);

			pstmt.setInt(1, id);
			pstmt.setString(2, message);

			pstmt.executeUpdate();

			pstmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static List<String> getAllMessages(int id) {
		String sql1 = "select message from messages where id=?";
		String sql2 = "delete from messages where id=?";
		Connection connection = DB.getConnection();
		List<String> messagesList = new ArrayList<String>();
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql1);
			pstmt.setInt(1, id);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				messagesList.add(rs.getString(1));
			}
			rs.close();
			pstmt.close();

			pstmt = connection.prepareStatement(sql2);
			pstmt.setInt(1, id);
			pstmt.executeUpdate();

			pstmt.close();
			connection.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return messagesList;
	}
}